﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Schema;

using System.Text.RegularExpressions;
using System.IO;
using System.Windows.Forms;
using System.Threading;
using SpectationClient;
using SpectationClient.Stuff;
using SpectationClient.SQLCommandBuilder;
using SpectationClient.DataBaseDescription;
using SpectationClient.DGVExtensions;
using SpectationClient.SpectralTools;
using SpectationClient.Async;
using SpectationClient.GUI.UC;
using Npgsql;

namespace SpectationClient.GUI {
    public partial class SubSearchResults : Form {


        private class DGV_LUT {

            public DGV_LUT(DataGridView dgv, TableInfo tableInfo=null, CheckBox checkBox=null
                        , String CSVTableName = null
                        ) {

                this.dataTable = new DataTable();

                this.dv = new DataView(this.dataTable);
                this.dgv = dgv;
                this.dgv.DataSource = this.dv;
                this.tableInfo = tableInfo;
                this.csvTableName = CSVTableName == null? dgv.Name : CSVTableName;
                this.checkBox = checkBox;
                this.isInitialized = false;
            }
            public DataGridView dgv;
            public DataView dv;
            public TableInfo tableInfo;
            public DataTable dataTable;
            public CheckBox checkBox;
            public Boolean isInitialized;
            public String csvTableName;
        }

        private DBManager DBM;
        private ErrorList EL;
        private NpgsqlConnection _Connection;
        private NpgsqlCommand _original_command;
        private NpgsqlCommand _main_command;
        private String        _main_command_description;
        //private PostGIS_Npgsql_CommandBuilder CMB;
        private List<Object> SPEC_IDS;
        private List<DGV_LUT> DGV_LUTList;

        private DataTable dtBT_Photos;
        private DataTable dtBT_WRef;

        private Regex REGEX_ENDDOTS = new Regex("[.]+$", RegexOptions.Singleline);
        private Regex REGEX_FILEPATH = new Regex("[;:/ ]", RegexOptions.Singleline);

        private DataTable CSV_SEP;
        private ViewSpectraForm SPECVIEWER_SURF;
        private ViewSpectraForm SPECVIEWER_WREF;
   
        
        const int C_DGV_maxHeight = 120;
        const int C_DGV_HeaderOffset = 40;
        const int C_DGV_RowHeight = 22;
       
        private bool showSelectedOnly = false;

        private AGetDatabaseObject AGDBO;
        private bool SQL_INIT_CORRECT = true;
      
        public SubSearchResults(NpgsqlCommand cmd, DBManager dbm, String cmdDescription=null) {
            if(cmd == null) throw new Exception("Command to search in SPECLIB does not exist");
            

            InitializeComponent();


            this.Icon = Resources.Icon;
            this.DBM = dbm;
            EL = new ErrorList();
            EL.ErrorAdded += new EventHandler(EL_ErrorAdded);
            EL.ErrorListIsEmpty += new EventHandler(EL_ErrorListIsEmpty);
            _Connection = dbm.Connection.Clone();
            cmd.Connection = _Connection;
            _original_command = cmd;
            _main_command_description = cmdDescription != null? cmdDescription : 
                                            SQLCommandBuilder.CommandBuilder.CommandToString(cmd);



            this.AGDBO = new AGetDatabaseObject();
            this.AGDBO.GetDataTableCompleted +=new GetDataTableCompletedEventHandler(AGDBO_GetDataTableCompleted);
            this.AGDBO.ProgressChanged +=new Async.ProgressChangedEventHandler(AGDBO_ProgressChanged);
            this.TIMER.Tick += new EventHandler(TIMER_Tick);
            
            //this.Disposed += new EventHandler(SubSearchResults_NEW_Disposed);
            //SPEC_dgv.RowsAdded += new DataGridViewRowsAddedEventHandler(DGV_NO_ROWS_Changed);
            tsbOFFSET_BACK.Enabled = false;
            tsbOFFSET_FORWARD.Enabled = false;
            tsbSearch.Enabled = false;
            btSaveALL.Enabled = false;
            this.tsTBLIMIT.Text = Properties.Settings.Default.DEF_ROWLIMIT.ToString();
            this.tstbOFFSET.Text = Properties.Settings.Default.DEF_ROWOFFSET.ToString();
            //ti_SPEC = new TableInfo("SPEC");
            //ti_SPEC.PK.Add("id");

           
            //connectDGV_with_GroupBoxes();
            this.DGV_LUTList = new List<DGV_LUT>();
            initDataViews();
            initMainTable();

            CSV_SEP = new DataTable();
            CSV_SEP.Columns.Add("SEP", typeof(String));
            CSV_SEP.Columns.Add("SHOW", typeof(String));
            CSV_SEP.Rows.Add(new object[] { "\relatedColumnType", "Tab" });
            CSV_SEP.Rows.Add(new object[] { ";", ";" });
            CSV_SEP.Rows.Add(new object[] { ",", "," });
            Save_SEP.DataSource = CSV_SEP;
            Save_SEP.DisplayMember = "SHOW";
            Save_SEP.ValueMember = "SEP";
        }

        void AGDBO_ProgressChanged(object sender, ProgressChangedEventArgs e) {
           
        }

        void AGDBO_GetDataTableCompleted(object sender, GetDataTableCompletedEventArgs e) {
            String tableName = (String)e.UserState;
            this.Cursor = Cursors.Default;
            if(e.Cancelled) {

            } else if(e.Error != null) {
                this.SQL_INIT_CORRECT = false;
                this.RTB.Text += TextHelper.Exception2String(e.Error);
            } else {
                if(DGV_LUTList.Exists(p => p.dgv.Name == tableName)) {
                    DGV_LUT info = DGV_LUTList.First(p => p.dgv.Name == tableName);

                    info.dataTable = e.DataTable;
                    info.isInitialized = true;
                    info.dataTable.TableName = info.dgv.Name;
                    FormHelper.initDGVColumns(info.dgv, info.dataTable, info.tableInfo, info.dv, this.DBM.Connection);

                    if(e.DataTable.Rows.Count > 0) info.checkBox.Checked = true;

                    if(tableName == SPEC_dgv.Name) {
                        if(e.DataTable.Rows.Count == 0) {
                            MessageBox.Show("SQL Anfrage lieferte keine Ergebnisse zurück"
                                , "Info"
                                , MessageBoxButtons.OK
                                , MessageBoxIcon.Information);
                            Enable_DisableRequests(true);
                            return;
                        } else {
                            Enable_DisableRequests(false);
                            SearchMetaInfos();
                        }
                    }
                } else {
                    switch(tableName) {
                        case "BT_PHOTOS": 
                            this.dtBT_Photos = e.DataTable; 
                            break;
                        case "BT_WREF":
                            this.dtBT_WRef = e.DataTable;
                            break;
                        default : //nothing
                            break;
                    }
                }
            }

            int nUnfinishedInitializations = (from x in DGV_LUTList
                         where x.isInitialized != true
                         select x).Count();
            if(dtBT_Photos == null)  nUnfinishedInitializations++;
            if(dtBT_WRef == null) nUnfinishedInitializations++;

            this.prog_bar.Minimum = 0;
            this.prog_bar.Maximum = DGV_LUTList.Count + 2;
            if(nUnfinishedInitializations != 0) {
                this.prog_bar.Value = this.prog_bar.Maximum - nUnfinishedInitializations;
            } else {
                this.prog_bar.Value = 0;
            }

            bool specIDsReturned = SPEC_dgv.RowCount > 0;
            Enable_DisableRequests(nUnfinishedInitializations == 0 || specIDsReturned == false);
        }

        void EL_ErrorListIsEmpty(object sender, EventArgs e) {
            Enable_DisableRequests(true);
        }

        void EL_ErrorAdded(object sender, EventArgs e) {
            Enable_DisableRequests(false);
        }

        void TIMER_Tick(object sender, EventArgs e) {
            String txt = this.prog_statuslabel.Text;
            if(txt != null) {
                System.Text.RegularExpressions.Match m = this.REGEX_ENDDOTS.Match(txt);
                if(m.Length < 3) {
                    this.prog_statuslabel.Text += '.';
                } else {
                    this.prog_statuslabel.Text = txt.Remove(txt.Length - 3);
                }
            }
        }


        private void Enable_DisableRequests(bool enabled) {
            bool is_ok = this.SQL_INIT_CORRECT && enabled;
            this.btSaveALL.Enabled = is_ok;
            tsbOFFSET_BACK.Enabled = is_ok;
            tsbOFFSET_FORWARD.Enabled = is_ok;
            tsbSearch.Enabled = is_ok;
            gb_CheckBoxes.Enabled = is_ok;
        }

       
        private void initDataViews() {
            //FormHelper.setLinkAction_SwitchVisualization(cb_SPEC, SPEC_gb);
            cb_SPEC.CheckedChanged += new EventHandler(cb_SPEC_CheckedChanged);
            SPEC_gb.SizeChanged += new EventHandler(SPEC_gb_SizeChanged);
            FormHelper.setLinkAction_SwitchVisualization(cb_LOCATIONS, gb_LOCATIONS);
            FormHelper.setLinkAction_SwitchVisualization(cb_OBSAREA, gb_OBSAREA);
            FormHelper.setLinkAction_SwitchVisualization(cb_VEG_PS, gb_VEG_PS);
            FormHelper.setLinkAction_SwitchVisualization(cb_VEG_SP, gb_VEG_SP);
            FormHelper.setLinkAction_SwitchVisualization(cb_WREF, gb_WREF);
            FormHelper.setLinkAction_SwitchVisualization(cb_PHOTOS, PHOTOS_gb);
            FormHelper.setLinkAction_SwitchVisualization(cb_SOILS, gb_SOILS);
            int maxHeight = 250;
            FormHelper.setFlexibleHeight(VEG_PS_dgv, 0, maxHeight);
            FormHelper.setFlexibleHeight(VEG_SP_dgv, 0, maxHeight);
            FormHelper.setFlexibleHeight(SPEC_dgv, 0, maxHeight);
            FormHelper.setFlexibleHeight(OBSAREA_dgv, 0, maxHeight);
            FormHelper.setFlexibleHeight(WR_dgv, 0, maxHeight);
            FormHelper.setFlexibleHeight(LOC_dgv, 0, maxHeight);
            FormHelper.setFlexibleHeight(PHOTOS_dgv, 0, maxHeight);
            FormHelper.setFlexibleHeight(SOILS_dgv, 0, maxHeight);

            DataBaseInfo DBINFO = this.DBM.getDataBaseInfo();
            
            //define which DGV is connected to which TableInfo
            this.DGV_LUTList.Add(new DGV_LUT(LOC_dgv, DBINFO["CORE"]["LOCATIONS"], cb_LOCATIONS, "Aufnahmepunkte"));
            this.DGV_LUTList.Add(new DGV_LUT(WR_dgv,  DBINFO["CORE"]["WREF_SPECTRA"], cb_WREF, "Weißreferenzen"));
            this.DGV_LUTList.Add(new DGV_LUT(SPEC_dgv, DBINFO["CORE"]["SPECTRA"], cb_SPEC, "Spektren" ));
            this.DGV_LUTList.Add(new DGV_LUT(OBSAREA_dgv, null, cb_OBSAREA, "Observationsgebiete" ));
            this.DGV_LUTList.Add(new DGV_LUT(VEG_PS_dgv, null, cb_VEG_PS, "Pflanzengesellschaften"));
            this.DGV_LUTList.Add(new DGV_LUT(VEG_SP_dgv, null, cb_VEG_SP, "Einzelplfanzenbeschreibungen"));
            this.DGV_LUTList.Add(new DGV_LUT(SOILS_dgv, null, cb_SOILS, "Böden"));
            this.DGV_LUTList.Add(new DGV_LUT(PHOTOS_dgv, DBINFO["CORE"]["PHOTOS"], cb_PHOTOS, "Fotos"));
           
        }

        void SPEC_gb_SizeChanged(object sender, EventArgs e) {
            Size s = SPEC_gb.Size;
            splitContainer1.SplitterDistance = s.Height + 10;
        }

        void cb_SPEC_CheckedChanged(object sender, EventArgs e) {
            bool is_checked = ((CheckBox) sender).Checked;
            if(is_checked) {
                splitContainer1.Panel1Collapsed = false;
                SPEC_gb_SizeChanged(null, null);
            } else {
                splitContainer1.Panel1Collapsed = true;
            }
        }

        
        

        void SearchMetaInfos() {   
            try {


                    this.SPEC_IDS = getDB_IDs(SPEC_dgv, "id", false);
                    if(this.SPEC_IDS.Count > 0) {
                        //
                        this.dtBT_Photos = null;
                        this.dtBT_WRef = null;
                        AGDBO.GetDataTableAsync(getCmdBT_Photos(this.SPEC_IDS), "BT_PHOTOS");
                        AGDBO.GetDataTableAsync(getCmdBT_WRef(this.SPEC_IDS), "BT_WREF");
                        AGDBO.GetDataTableAsync(getCmdVEG_PS(this.SPEC_IDS), VEG_PS_dgv.Name);
                        AGDBO.GetDataTableAsync(getCmdVEG_SP(this.SPEC_IDS), VEG_SP_dgv.Name);
                        AGDBO.GetDataTableAsync(getCmdWREF(this.SPEC_IDS), WR_dgv.Name);
                        AGDBO.GetDataTableAsync(getCmdPHOTOS(this.SPEC_IDS), PHOTOS_dgv.Name);
                        AGDBO.GetDataTableAsync(getCmdOBSERVATION_AREAS(this.SPEC_IDS), OBSAREA_dgv.Name);
                        AGDBO.GetDataTableAsync(getCmdSOILS(this.SPEC_IDS), SOILS_dgv.Name);
                        AGDBO.GetDataTableAsync(getCmdLOCATIONS(this.SPEC_IDS), LOC_dgv.Name);
                    }
            } catch (Exception ex) {
                FormHelper.ShowErrorBox(ex);
            }
        }

        private NpgsqlCommand getCmdVEG_SP(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText = String.Format("SELECT * FROM \"C_GFZ\".\"VIEW_SPEC2PLANTS\" WHERE {0}", cmd.CommandText);
            cmd.Connection = _Connection;
            return cmd;
        }

        private NpgsqlCommand getCmdSOILS(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText = String.Format("SELECT * FROM \"C_GFZ\".\"VIEW_SPEC2SOILS\" WHERE {0}", cmd.CommandText);
            cmd.Connection = _Connection;
            return cmd;
        }
        private NpgsqlCommand getCmdVEG_PS(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText = String.Format("SELECT * FROM \"C_GFZ\".\"VIEW_SPEC2PLANT_SOCIETIES\" WHERE {0}", cmd.CommandText);
            cmd.Connection = _Connection;
            return cmd;
        }

       

        private NpgsqlCommand getCmdLOCATIONS(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText = String.Format("SELECT * FROM \"CORE\".\"VIEW_SPEC2LOCATIONS\" WHERE {0}", cmd.CommandText);
            cmd.Connection = _Connection;
            return cmd;
        }

        private NpgsqlCommand getCmdOBSERVATION_AREAS(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText = String.Format("SELECT * FROM \"C_GFZ\".\"VIEW_SPEC2OBSERVATION_AREAS\" WHERE {0}", cmd.CommandText);
            cmd.Connection = _Connection;
            return cmd;
        }

        private NpgsqlCommand getCmdBT_Photos(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText = "SELECT * FROM \"CORE\".\"BT_PHOTOS\" WHERE " + cmd.CommandText;
            cmd.Connection = _Connection;
            return cmd;
        }

        private NpgsqlCommand getCmdBT_WRef(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText = "SELECT * FROM \"CORE\".\"BT_WREF_SPECTRA\" WHERE " + cmd.CommandText;
            cmd.Connection = _Connection;
            return cmd;
        }

        private NpgsqlCommand getCmdPHOTOS(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("BT.id_spectrum", Condition.Op.EQ, spec_ids).getCmd();

            cmd.CommandText =
                "SELECT DISTINCT "+
                "PH.id AS id, "+
                "null::bytea AS file, "+
                //"PH.file AS file, "+
                "PH.filepreview AS filepreview, "+
                "PH.filename AS filename, "+
                "PH.purpose AS purpose, "+
                "PH.x AS res_x, "+
                "PH.y AS res_y, "+
                "PH.dpi AS dpi, "+
                "PH.datetime AS datetime, "+
                "PH.notes AS notes, "+
                "CA.id AS camera_id, "+
                "CA.name AS camera_name, "+
                "CA.owner AS camera_owner, "+
                "CA.notes AS camera_notes, "+
                "OP.id AS operator_id, "+
                "OP.surname AS operator_surname, "+
                "OP.forename AS operator_forename, "+
                "OP.institute AS operator_institute, "+
                "OP.workgroup AS operator_workgroup, "+
                "OP.notes AS operator_notes "+
                "FROM \"CORE\".\"BT_PHOTOS\" AS BT "+
                "INNER JOIN \"CORE\".\"PHOTOS\"    AS PH ON BT.id_photo = PH.id "+
                "LEFT JOIN \"CORE\".\"OPERATORS\" AS OP ON PH.id_operator = OP.id "+
                "LEFT JOIN \"CORE\".\"CAMERAS\"   AS CA ON PH.id_camera = CA.id "+
                "WHERE "+ cmd.CommandText;

            /*
            cmd.CommandText =
                "SELECT  "+
                "BT.id_spectrum AS id_spectrum,"+
                "CA.id AS camera_id, "+
                "CA.name AS camera_name, "+
                "CA.owner AS camera_owner, "+
                "CA.notes AS camera_notes, "+
                "OP.id AS operator_id, "+
                "OP.surname AS operator_surname, "+
                "OP.forename AS operator_forename, "+
                "OP.institute AS operator_institute, "+
                "OP.workgroup AS operator_workgroup, "+
                "OP.notes AS operator_notes, "+
                "PH.id AS id, "+
                "PH.filepreview AS filepreview, "+
                "PH.filename AS filename, "+
                "PH.purpose AS purpose, "+
                "PH.x AS res_x, "+
                "PH.y AS res_y, "+
                "PH.dpi AS dpi, "+
                "PH.datetime AS datetime, "+
                "PH.notes AS notes "+
                "FROM \"CORE\".\"BT_PHOTOS\" AS BT "+
                "INNER JOIN \"CORE\".\"PHOTOS\"    AS PH ON BT.id_photo = PH.id "+
                "LEFT JOIN \"CORE\".\"OPERATORS\" AS OP ON PH.id_operator = OP.id "+
                "LEFT JOIN \"CORE\".\"CAMERAS\"   AS CA ON PH.id_camera = CA.id "+
                "WHERE "+ cmd.CommandText;
             */
            cmd.Connection = _Connection;
            return cmd;
        }
        private NpgsqlCommand getCmdWREF(List<Object> spec_ids) {
            NpgsqlCommand cmd = new Condition("id_spectrum", Condition.Op.EQ, spec_ids).getCmd();
            cmd.CommandText =
                "SELECT distinct " +
                "WR.id, " +
                "WR.id_panel, " +
                "WR.id_sensor, " +
                "WR.file, " +
                "WR.file_hdr, " +
                "WR.filename, " +
                "WR.filetype, " +
                "WR.spectype, " +
                "WR.datatype, " +
                "WR.datetime, " +
                "WR.notes  " +
                "FROM \"CORE\".\"BT_WREF_SPECTRA\" AS BT " +
                "INNER JOIN \"CORE\".\"WREF_SPECTRA\"    AS WR ON BT.id_wref = WR.id " +
                "WHERE " + cmd.CommandText;
            cmd.Connection = _Connection;
            
            return cmd;
        }

         


        /// <summary>
        /// This command loads the constrained CORE.SPECTRA Table from SPECLIB
        /// </summary>
        private void initMainTable() {
            if (_main_command != null) _main_command.Dispose();
            _main_command = _original_command.Clone();
            _main_command.CommandText += String.Format(" LIMIT {0} OFFSET {1}", tsTBLIMIT.Text, tstbOFFSET.Text);
            this.RTB.Clear();
            for(int i=0; i < this.DGV_LUTList.Count; i++ ) {
                DGV_LUT info =  DGV_LUTList[i];
                info.isInitialized = false;
                DGV_LUTList[i].dgv.DataSource = null;

            }
            Enable_DisableRequests(false);
            this.Cursor = Cursors.WaitCursor;
            AGDBO.GetDataTableAsync(_main_command, SPEC_dgv.Name);
        }

        private List<Spectrum> getSPECTRAFromDGV(ref DataGridView dgv, String id_column, String filecolumn, bool selected_only){
            List<Spectrum> list =new List<Spectrum>();
            if(dgv.Columns[filecolumn].GetType() != typeof(DataGridViewSpectrumColumn)) {
                throw new Exception(String.Format("Class: {0}\nFunction: {1}\nError: {2}",
                    "SubSearchResults", "getSPECTRAFromDGV(...)",
                    "DGV-Column is not a DataGridViewSpectrumColumn!"));
            }
            int idx = dgv.Columns[filecolumn].Index;

            foreach (DataGridViewRow r in dgv.Rows) {
                String str_new_name = "";

                DataGridViewSpectrumCell cell = (DataGridViewSpectrumCell)r.Cells[idx];
                if(!selected_only || selected_only && r.Selected){
                    Spectrum spec = cell.getSpectrum();
                    if (spec != null) {
                        str_new_name = String.Format("ID:{0} ({1}:{2})", r.Cells[id_column].Value, spec.FileName, spec.SpectrumName);
                        //String str_new_description = String.Format("SPECLIB ID:{0} {1})", result.Cells[id_column].Value, ba.COMMENTS);
                        spec.SpectrumName = str_new_name;
                        //ba.COMMENTS = str_new_description;
                        list.Add(spec);
                    } 
                }
            }
            return list;
        }

        private String getDVFilter(DataGridView dgv, String id_column_dgv, String id_column_DV) {
            if(dgv.Columns.Contains(id_column_dgv)){
                List<Object> idList = getDB_IDs(dgv, id_column_dgv, true);
                    return getDVFilter(idList, id_column_DV);
            }
            return "";
        }
        private String getDVFilter(DataTable dt, String id_column_dt, String id_column_DV) {
            if(dt.Columns.Contains(id_column_dt)) {
                List<Object> idList = getDB_IDs(dt, id_column_dt);
                return getDVFilter(idList, id_column_DV);
            }
            return "";
        }

        private String getDVFilter(List<Object> idList, String idColumnDV) {
            StringBuilder SB = new StringBuilder();
            for(int i=0; i < idList.Count; i++) {
                SB.AppendFormat("{0} = {1}", idColumnDV, idList[i].ToString());
                if(i < idList.Count - 1) SB.Append(" OR ");
            }
            if(SB.Length == 0) SB.AppendFormat("1 = 2");
            return SB.ToString();
        }

        private List<Object> getDB_IDs(DataTable dt, String id_column) {
            return (from DataRow row in dt.Rows
                    where row[id_column] != null &&
                          row[id_column] != DBNull.Value
                    select row[id_column]).Distinct().ToList();
        }

        private List<Object> getDB_IDs(DataGridView dgv, String id_column, bool selected_only) {
            List<Object> ids = new List<Object>();
            int iColumn = dgv.Columns[id_column].Index;
            return (from DataGridViewRow r in dgv.Rows
                    where (!selected_only || selected_only && r.Selected) &&
                            r.Cells[iColumn].Value != null &&
                            r.Cells[iColumn].Value != DBNull.Value
                    select r.Cells[iColumn].Value).Distinct().ToList();
        }

        /// <summary>
        /// Starts a BackgroundWorker that saves the content of all DataGridViews.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btSaveAll_Click(object sender, EventArgs e) {
            
            FBD.Description = "Bitte Hauptverzeichnis zum Speichern der Daten angeben";
            FBD.ShowNewFolderButton = true;
          
            String sep = Save_SEP.SelectedValue.ToString();
            if (FBD.ShowDialog() == DialogResult.OK) {
                this.Cursor = Cursors.WaitCursor;
                BackgroundWorkerProgressBar BGWF = new BackgroundWorkerProgressBar();
                BGWF.Title = "Daten Speichern";
                BGWF.InfoText = "Speichere Suchergebnisse...";
                BackgroundWorker BGW = BGWF.BackgroundWorker;
                BGW.DoWork +=new DoWorkEventHandler(BGW_DoWork);
                BGW.RunWorkerCompleted +=new RunWorkerCompletedEventHandler(BGW_RunWorkerCompleted);
               
                
                BGWArgument arg = new BGWArgument();
                arg.dgvList = this.DGV_LUTList;
                arg.RootDir = FBD.SelectedPath;
                arg.SaveDBPhotos = btSaveFotosFromDB.Checked;
                arg.SaveSelectedOnly = cbSaveSelectedOnly.Checked;
                if(Save_FileFormat_ASD.Checked) {
                    arg.SpectrumFileType = Spectrum.SpectrumFileType.ASD;
                } else {
                    arg.SpectrumFileType = Spectrum.SpectrumFileType.ESL;
                }
                BGW.RunWorkerAsync(arg);
 
            }
        }

        void BGW_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
            if(e.Error != null) {
                FormHelper.ShowErrorBox(e.Error);
            } else {
                if(e.Cancelled) {

                } else {
                    MessageBox.Show("Daten gespeichert", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                
            }
            this.Cursor = Cursors.Default;
        }

        private struct BGWArgument {
            
            public string RootDir {get; set;}
            public bool SaveSelectedOnly {get;set;}
            public bool SaveDBPhotos {get;set;}
            public SpectralTools.Spectrum.SpectrumFileType SpectrumFileType { get; set; }
            public List<DGV_LUT> dgvList {get;set;}
            

        
        }

        void BGW_DoWork(object sender, DoWorkEventArgs e) {
            BackgroundWorker bgw = (BackgroundWorker)sender;
            BGWArgument arg = (BGWArgument)e.Argument;
            System.Drawing.Imaging.ImageFormat imageFormat = System.Drawing.Imaging.ImageFormat.Png;
           
            int cnt = 0;
                foreach(DGV_LUT info in arg.dgvList) {
                        if(info.dgv == null) continue;
                        if(info.dgv.RowCount == 0) continue;
                        if(arg.SaveSelectedOnly && info.dgv.SelectedRows.Count == 0) continue;

                        FileInfo newCSVTable = new FileInfo(arg.RootDir + "\\" + info.csvTableName + ".csv");
                        
                        bgw.ReportProgress((int)((float)cnt / arg.dgvList.Count * 100), newCSVTable.Name);
                     
                        FormHelper.saveDGV(info.dgv, newCSVTable, arg.SaveSelectedOnly, arg.SaveDBPhotos);
                       
                        if(info.dgv.Name == LOC_dgv.Name) { 
                            FileInfo newKMLFile = new FileInfo(arg.RootDir + "\\koordinaten.kml");

                            bgw.ReportProgress((int)((float)cnt / arg.dgvList.Count * 100), newKMLFile.Name);
                            String[] infoColumns = new String[] { "id", "name", "height", "slope"
                                                                , "longitude", "latitude", "notes" };
                                
                            FormHelper.saveDGVasKML(info.dgv, newKMLFile, "id", "longitude", "latitude",
                                infoColumns, arg.SaveSelectedOnly);

                        }

                    
                        if(bgw.CancellationPending) {
                            e.Cancel = true;
                            return;
                        }
                    
                    cnt++;
                }
            
            #if DEBUG
                Console.WriteLine("BGW Work is done.");
            #endif
        }


        void set_ValidationEventHandler(object sender, ValidationEventArgs e) {
            if(e.Severity == XmlSeverityType.Warning)
                Console.WriteLine("\tWarning: Matching Schema not found.  No validation occurred." + e.Message);
            else
                Console.WriteLine("\tValidation error: " + e.Message);

        }

        private void SPEC_showBt_Click(object sender, EventArgs e) {
            
            if (SPEC_dgv.Columns.Contains("file")) {
                if (SPECVIEWER_SURF == null) {
                    try {
                        this.SPECVIEWER_SURF = new ViewSpectraForm();
                        this.SPECVIEWER_SURF.linkToSpectrumColumn((DataGridViewSpectrumColumn)SPEC_dgv.Columns["file"]);
                        this.SPECVIEWER_SURF.Disposed += (sender1, e1) => this.SPECVIEWER_SURF = null;
                        this.SPECVIEWER_SURF.Show();
                    } catch (Exception ex) {
                        Console.WriteLine(TextHelper.Exception2String(ex));
                        //NOTHING
                    }
                } else {
                    this.SPECVIEWER_SURF.BringToFront();
                }
            }
        }

        private void WR_showBt_Click(object sender, EventArgs e) {
            if (WR_dgv.Columns.Contains("file")) {
                if (this.SPECVIEWER_WREF == null) {
                    this.SPECVIEWER_WREF = new ViewSpectraForm();
                    this.SPECVIEWER_WREF.TopMost = true;
                    this.SPECVIEWER_WREF.linkToSpectrumColumn((DataGridViewSpectrumColumn)WR_dgv.Columns["file"]);
                    this.SPECVIEWER_WREF.Disposed += (sender1, e1) => this.SPECVIEWER_WREF = null;
                    this.SPECVIEWER_WREF.Show();
                }else{
                    this.SPECVIEWER_WREF.BringToFront();
                }
            }
        }

      
        private void tsmSearchParameters_Click(object sender, EventArgs e) {
            RTBForm RF = new RTBForm(this._main_command_description, "Einschränkungen der Suchanfrage");
            RF.Show();
        }

        private void tsmSQLCommandString_Click(object sender, EventArgs e) {
            RTBForm RF = new RTBForm(
               CommandBuilder.CommandToString(_main_command),
                "Verwendete SQL Anfrage");
            RF.Show();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void rbShowResults_checkedChanged(object sender, EventArgs e) {
            if (rbShowALL.Checked) {
                rbShowSelectedOnly.Checked = false;
                showSelectedOnly = false;
            } else {
                showSelectedOnly = true;
            }
            SPEC_dgv_SelectionChanged(null, null);
        }

        /// <summary>
        /// Toggles the data views between
        ///     mode 1: show all entries
        ///     mode 2: show only entries with id_spectrum == id_spectrum in the main table.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void SPEC_dgv_SelectionChanged(object sender, EventArgs e) {
            var dgvs = from x in this.DGV_LUTList
                       where x.dgv.Name != SPEC_dgv.Name 
                       select x;

            if (!showSelectedOnly) {
                foreach(var x in dgvs) {
                    x.dv.RowFilter = "";
                }

            } else {
                List<Object> idSelectedSpectra = getDB_IDs(SPEC_dgv, "id", true);
                List<Object> idRelatedPhotos = (from DataRow row in dtBT_Photos.Rows
                                         where idSelectedSpectra.Contains(row["id_spectrum"])
                                         select row["id_photo"]).Distinct().ToList();
                List<Object> idRelatedWRef = (from DataRow row in dtBT_WRef.Rows
                                              where idSelectedSpectra.Contains(row["id_spectrum"])
                                              select row["id_wref"]).Distinct().ToList();

                String filter_spec = getDVFilter(idSelectedSpectra, "id_spectrum");
                String filter_photos = getDVFilter(idRelatedPhotos, "id");
                String filter_wref = getDVFilter(idRelatedWRef, "id");

                foreach(var x in dgvs) {
                    if(x.dgv.Name == PHOTOS_dgv.Name){
                        x.dv.RowFilter = filter_photos;
                    }else if(x.dgv.Name == WR_dgv.Name){
                        x.dv.RowFilter = filter_wref;
                    }else if(x.dataTable.Columns.Count > 0 && 
                                x.dataTable.Columns.Contains("id_spectrum")) {
                        x.dv.RowFilter = filter_spec;
                    }
                    
                }


                
            }

            SPEC_bt_view.Enabled = SPEC_dgv.SelectedRows.Count > 0;
        }

        private void tstbOFFSETLIMIT_TextChanged(object sender, EventArgs e) {
            Int16 val;
            if(Int16.TryParse(((ToolStripTextBox)sender).Text, out val) && val >= 0){
                EL.removeError(sender);
            }else{
                EL.addError(sender, "Benötigt eine natürliche Zahl >= 0");
            }
        }

        private void tsbOFFSET_BACK_Click(object sender, EventArgs e) {
            Int16 off_old = Int16.Parse(tstbOFFSET.Text);
            Int16 limit = Int16.Parse(tsTBLIMIT.Text);
            int off_new = off_old - limit;
            if (off_new < 0) off_new = 0;
            tstbOFFSET.Text = off_new.ToString();
            tsbSearch_Click(null, null);
        }

        private void tsbOFFSET_FORWARD_Click(object sender, EventArgs e) {
            Int16 off_old = Int16.Parse(tstbOFFSET.Text);
            Int16 limit = Int16.Parse(tsTBLIMIT.Text);
            int off_new = off_old + limit;
            tstbOFFSET.Text = off_new.ToString();
            tsbSearch_Click(null, null);
        }

        private void tsbSearch_Click(object sender, EventArgs e) {
            //SPEC_dgv.DataSource = null; ;
            initMainTable();
        }

        private void WR_dgv_SelectionChanged(object sender, EventArgs e) {
            WR_showBt.Enabled = WR_dgv.SelectedRows.Count > 0;
        }

        private void PHOTOS_dgv_SelectionChanged(object sender, EventArgs e) {
            PHOTOS_showBt.Enabled = PHOTOS_dgv.SelectedRows.Count > 0;
        }

        private void PHOTOS_showBt_Click(object sender, EventArgs e) {
            if(PHOTOS_dgv.Columns.Contains("file") && PHOTOS_dgv.Columns["file"] is DataGridViewPhotoColumn) {
                DataGridViewPhotoColumn pc = (DataGridViewPhotoColumn) PHOTOS_dgv.Columns["file"];
                pc.showSelectedPhoto();
            
            }
        }


    }
}
